using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace ITG.Modules.HR.Components.SmartBoss
{
    public class TimeCard
    {
        public static DataSet GetBy(string[] departmentID, DateTime fromDate, DateTime toDate)
        {
            string query = "SELECT ";
            query += "C.cIndex, ";
            query += "E.cID AS CardID, ";
            query += "D.cDepartmentID AS DepartmentID, ";
            query += "D.cDepartment AS DepartmentName, ";
            query += "E.cFirstName AS FullName, ";
            query += "C.cDateTime AS WorkingDate, ";
            query += "C.cTimeIn AS CheckIn, ";
            query += "C.cTimeOutMid, ";
            query += "C.cTimeInMid, ";
            query += "C.cTimeOut AS CheckOut, ";
            query += "C.cNormalTime AS Hours, ";
            query += "C.cOverTime AS OverTime1, ";
            query += "C.cOverTime2 AS OverTime2 ";

            query += "FROM ";
            query += "tblTimeCard AS C ";
            query += "INNER JOIN tblEmployee AS E ON C.cID = E.cID ";
            query += "INNER JOIN tblDepartment AS D ON E.cDepartmentID = D.cDepartmentID ";

            query += "WHERE ";
            query += "(C.cTimeIn IS NOT NULL) AND (C.cTimeOut IS NOT NULL) ";
            query += "AND (C.cDateTime BETWEEN @FromDate AND @ToDate) ";

            if (departmentID.Length > 0)
            {
                query += "AND (";
                foreach (string id in departmentID)
                {
                    query += string.Format("(E.cDepartmentID = {0}) OR ", id);
                }
                query = query.Remove(query.Length - 4, 4);
                query += ") ";
            }
            else
            {
                query += "AND (1 = 2) ";
            }
            query += "ORDER BY ";
            query += "CardID, WorkingDate";

            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase("SmartBoss");
            SqlCommand dbCommand = (SqlCommand) db.GetSqlStringCommand(query);

            db.AddInParameter(dbCommand, "@FromDate", SqlDbType.DateTime, fromDate);
            db.AddInParameter(dbCommand, "@ToDate", SqlDbType.DateTime, toDate);

            return db.ExecuteDataSet(dbCommand);
        }
    }
}